# -*- coding: utf-8 -*-
import boto3
# import sys
import pandas as pd


lbclient = boto3.client('elbv2')
es2client = boto3.client('ec2')

# lb_name = sys.argv[1]
# tg_name = sys.argv[2]
# tg_port = int(sys.argv[3])


# parameter lb_name SLB 名称，lb_globle 公网或私网， lb_type TCP 或 HTTP
def creatlb(lb_name, lb_globle, lb_type):
    create_load_balancer = lbclient.create_load_balancer(
        Name=lb_name,
        Scheme=lb_globle,
        SecurityGroups=[
            'sg-03c7025177a304f96',     # ELB所在安全组ID
        ],
        Subnets=[
            'subnet-0dfa0ee114c599403',     # ELB所在子网ID 1
            'subnet-076a047f22a0189b5',     # ELB所在子网ID 2
            'subnet-0f14adc94c73b0304',     # ELB所在子网ID 3
        ],
        Type=lb_type,
    )
    lb_arn = create_load_balancer['LoadBalancers'][0]['LoadBalancerArn']
    lb_dns = create_load_balancer['LoadBalancers'][0]['DNSName']
    return lb_arn, lb_dns


def creatlb_tcp(lb_name, lb_globle, lb_type):
    create_load_balancer = lbclient.create_load_balancer(
        Name=lb_name,
        Scheme=lb_globle,
        Subnets=[
            'subnet-0dfa0ee114c599403',     # ELB所在子网ID 1
            'subnet-076a047f22a0189b5',     # ELB所在子网ID 2
            'subnet-0f14adc94c73b0304',     # ELB所在子网ID 3
        ],
        Type=lb_type,
    )
    lb_arn = create_load_balancer['LoadBalancers'][0]['LoadBalancerArn']
    lb_dns = create_load_balancer['LoadBalancers'][0]['DNSName']
    return lb_arn, lb_dns


# tg_name 目标组名称，tg_port 目标组端口， lb_protocol 目标组协议类型(http,tcp,https等)
def createtg(tg_name, tg_port, lb_protocol, healthpath):
    create_target_group = lbclient.create_target_group(
        Name=tg_name,
        Port=tg_port,
        Protocol=lb_protocol,
        VpcId='vpc-085460d46def41974',  # VPC ID
        HealthCheckPath=healthpath,
    )
    tg_arn = create_target_group['TargetGroups'][0]['TargetGroupArn']
    return tg_arn


def createtg_tcp(tg_name, tg_port, lb_protocol):
    create_target_group = lbclient.create_target_group(
        Name=tg_name,
        Port=tg_port,
        Protocol=lb_protocol,
        VpcId='vpc-085460d46def41974',  # VPC ID
    )
    tg_arn = create_target_group['TargetGroups'][0]['TargetGroupArn']
    return tg_arn


def createlt(lb_arn, tg_arn, lt_port, lb_protocol):
    crete_listener = lbclient.create_listener(
        DefaultActions=[
            {
                'TargetGroupArn': tg_arn,
                'Type': 'forward',
            },
        ],
        LoadBalancerArn=lb_arn,
        Port=lt_port,
        Protocol=lb_protocol,
    )
    return crete_listener


def getec2info(privat_ip_adderss):
    describe_instances = es2client.describe_instances(
        Filters=[
            {
                'Name': 'network-interface.addresses.private-ip-address',
                'Values': [
                    privat_ip_adderss,
                ]
            },
        ],
    )
    return describe_instances['Reservations'][0]['Instances'][0]['InstanceId']


if __name__ == "__main__":

    # ip = '10.6.1.127'

    # print(getec2info(ip))

    read_excel_file = 'data.xlsx'
    write_excel_file = 'out_data.xlsx'

    df = pd.DataFrame(pd.read_excel(read_excel_file, '审批'))

    nrows = df.shape[0]
    ncols = df.columns.size
    tmp_iloc = df.iloc()

    # 主机编号、LB编号
    host_row_num = 1
    elb_row_num = 1
    for irow in range(nrows):
        if '-slb-' in tmp_iloc[irow, 3]:
            # df.iloc[irow,0] = 'This is SLB'
            lb_name = tmp_iloc[irow, 3]
            lb_globe = 'internal'
            tg_port = int(tmp_iloc[irow, 6])
            if tmp_iloc[irow, 8] == 'tcp':
                lb_type = 'network'
                lb_protocol = 'TCP'
                tg_name = "{}-tcp".format(tmp_iloc[irow, 3])
                elb = creatlb_tcp(lb_name, lb_globe, lb_type)
                tg_arn = createtg_tcp(tg_name, tg_port, lb_protocol)
            else:
                lb_type = 'application'
                lb_protocol = 'HTTP'
                tg_name = "{}-http".format(tmp_iloc[irow, 3])
                elb = creatlb(lb_name, lb_globe, lb_type)
                healthpath = tmp_iloc[irow, 9]
                tg_arn = createtg(tg_name, tg_port, lb_protocol, healthpath)
            # elb = ['this is test', 'the test']

            # 创建转发规则
            lb_arn = elb[0]
            lb_dns = elb[1]
            lt_port = int(tmp_iloc[irow, 7])
            createlt(lb_arn, tg_arn, lt_port, lb_protocol)

            # 写入 DataFrame
            if elb_row_num < 10:
                tmp_iloc[irow, 0] = "ELB-0{}".format(elb_row_num)
            else:
                tmp_iloc[irow, 0] = "ELB-{}".format(elb_row_num)
            tmp_iloc[irow, 1] = lb_arn
            tmp_iloc[irow, 2] = lb_dns
            tmp_iloc[irow, 4] = lb_dns
            elb_row_num += 1

        if '-app-' in df.iloc[irow, 3]:
            # df.iloc[irow, 0] = 'this is host'
            if host_row_num < 10:
                tmp_iloc[irow, 0] = "Host-0{}".format(host_row_num)
            else:
                tmp_iloc[irow, 0] = "Host-{}".format(host_row_num)
            host_row_num += 1

            tmp_iloc[irow, 1] = getec2info(tmp_iloc[irow, 4])
            # tmp_iloc[irow, 1] = str(host_row_num)
    print(df)

    df.to_excel(write_excel_file, index=False)
    print('操作完成！')
